package org.wheel.plugins.toolkit.util;

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.*;
import org.junit.jupiter.api.Test;
import org.wheel.plugins.toolkit.util.sql.SQLHelper;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 基于jsqlparser 5.3+版本，查一个sql语句中，查询了多少个表，且查询的字段，都引用了几个表？
 */
public class SQLTableAnalyzer {

    /**
     * 分析SQL语句中的表和字段引用信息
     * @param sql SQL语句
     * @return 分析结果
     * @throws JSQLParserException 解析异常
     */
    public static SQLAnalysisResult analyzeSQL(String sql) throws JSQLParserException {
        Statement statement = CCJSqlParserUtil.parse(sql);
        SQLAnalysisResult result = new SQLAnalysisResult();

        if (statement instanceof Select) {
            Select select = (Select) statement;
            analyzeSelect(select, result);
        }

        return result;
    }

    private static void analyzeSelect(Select select, SQLAnalysisResult result) {
        if (select.getSelectBody() instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            analyzePlainSelect(plainSelect, result);
        } else if (select.getSelectBody() instanceof SetOperationList) {
            SetOperationList setOperationList = (SetOperationList) select.getSelectBody();
            // 处理UNION等集合操作
            for (Select selectBody : setOperationList.getSelects()) {
                if (selectBody instanceof PlainSelect) {
                    analyzePlainSelect((PlainSelect) selectBody, result);
                }
            }
        }
    }

    private static void analyzePlainSelect(PlainSelect plainSelect, SQLAnalysisResult result) {
        // 收集FROM子句中的表
        collectFromTables(plainSelect, result);

        // 收集SELECT子句中的字段
        collectSelectItems(plainSelect, result);

        // 收集WHERE子句中的字段
        collectWhereColumns(plainSelect, result);

        // 收集ORDER BY子句中的字段
        collectOrderByColumns(plainSelect, result);

        // 收集GROUP BY子句中的字段
        collectGroupByColumns(plainSelect, result);
    }

    private static void collectFromTables(PlainSelect plainSelect, SQLAnalysisResult result) {
        // 主表
        if (plainSelect.getFromItem() instanceof Table) {
            Table table = (Table) plainSelect.getFromItem();
            String tableName = table.getName();
            String alias = table.getAlias() != null ? table.getAlias().getName() : tableName;
            result.addTable(tableName, alias);
        }

        // JOIN表
        if (plainSelect.getJoins() != null) {
            for (Join join : plainSelect.getJoins()) {
                if (join.getRightItem() instanceof Table) {
                    Table table = (Table) join.getRightItem();
                    String tableName = table.getName();
                    String alias = table.getAlias() != null ? table.getAlias().getName() : tableName;
                    result.addTable(tableName, alias);
                }
            }
        }
    }

    private static void collectSelectItems(PlainSelect plainSelect, SQLAnalysisResult result) {
        for (SelectItem<?> selectItem : plainSelect.getSelectItems()) {
            if (selectItem.getExpression() instanceof Column) {
                Column column = (Column) selectItem.getExpression();
                result.addColumnReference(column);
            }
        }
    }

    private static void collectWhereColumns(PlainSelect plainSelect, SQLAnalysisResult result) {
        if (plainSelect.getWhere() != null) {
            plainSelect.getWhere().accept(new net.sf.jsqlparser.expression.ExpressionVisitorAdapter<Void>() {
                @Override
                public <S> Void visit(Column column, S context) {
                    result.addColumnReference(column);
                    return super.visit(column, context);
                }
            }, null);
        }
    }

    private static void collectOrderByColumns(PlainSelect plainSelect, SQLAnalysisResult result) {
        if (plainSelect.getOrderByElements() != null) {
            for (OrderByElement orderByElement : plainSelect.getOrderByElements()) {
                orderByElement.getExpression().accept(new net.sf.jsqlparser.expression.ExpressionVisitorAdapter<Void>() {
                    @Override
                    public <S>  Void visit(Column column, S context) {
                        result.addColumnReference(column);
                        return super.visit(column, context);
                    }
                }, null);
            }
        }
    }

    private static void collectGroupByColumns(PlainSelect plainSelect, SQLAnalysisResult result) {
        if (plainSelect.getGroupBy() != null && plainSelect.getGroupBy().getGroupByExpressions() != null) {
            for (Object expression : plainSelect.getGroupBy().getGroupByExpressions()) {
                if (expression instanceof Column) {
                    result.addColumnReference((Column) expression);
                }
            }
        }
    }

    /**
     * SQL分析结果类
     */
    public static class SQLAnalysisResult {
        // 表信息：表名 -> 别名
        private final Map<String, String> tables = new HashMap<>();
        // 字段引用信息：字段名 -> [表别名]
        private final Map<String, Set<String>> columnReferences = new HashMap<>();

        public void addTable(String tableName, String alias) {
            tables.put(tableName, alias);
        }

        public void addColumnReference(Column column) {
            String columnName = column.getColumnName();
            String tableAlias = column.getTable() != null ? column.getTable().getName() : "";

            columnReferences.computeIfAbsent(columnName, k -> new HashSet<>()).add(tableAlias);
        }

        /**
         * 获取表的数量
         * @return 表数量
         */
        public int getTableCount() {
            return tables.size();
        }

        /**
         * 获取查询中涉及的表名列表
         * @return 表名列表
         */
        public Set<String> getTableNames() {
            return new HashSet<>(tables.keySet());
        }

        /**
         * 获取字段引用的表信息
         * @return 字段引用的表信息
         */
        public Map<String, Set<String>> getColumnReferences() {
            return new HashMap<>(columnReferences);
        }

        /**
         * 获取字段引用了多少个不同的表
         * @return 引用的表数量
         */
        public int getReferencedTableCount() {
            Set<String> referencedTables = new HashSet<>();
            for (Set<String> tableAliases : columnReferences.values()) {
                referencedTables.addAll(tableAliases);
            }
            // 移除空字符串（表示没有明确指定表的字段）
            referencedTables.remove("");
            return referencedTables.size();
        }

        /**
         * 获取引用特定表的字段数量
         * @param tableName 表名或别名
         * @return 字段数量
         */
        public int getColumnCountForTable(String tableName) {
            int count = 0;
            for (Set<String> tableAliases : columnReferences.values()) {
                if (tableAliases.contains(tableName)) {
                    count++;
                }
            }
            return count;
        }

        @Override
        public String toString() {
            StringBuilder sb = new StringBuilder();
            sb.append("SQL分析结果:\n");
            sb.append("表数量: ").append(getTableCount()).append("\n");
            sb.append("表列表: ").append(getTableNames()).append("\n");
            sb.append("字段引用的表数量: ").append(getReferencedTableCount()).append("\n");
            sb.append("字段引用详情: ").append(getColumnReferences()).append("\n");
            return sb.toString();
        }
    }

    // 使用示例
    public static void main(String[] args) {
        try {
            // 测试SQL
//            String sql = "SELECT t1.name, t2.age, t3.salary " +
//                    "FROM users t1 " +
//                    "JOIN departments t2 ON t1.dept_id = t2.id " +
//                    "LEFT JOIN salaries t3 ON t1.id = t3.user_id " +
//                    "WHERE t1.status = 'active' AND t2.name = 'IT' " +
//                    "ORDER BY t1.name, t3.salary";

            String sql = "SELECT a.id, b.name, (SELECT MAX(score) FROM scores WHERE user_id = a.id) AS max_score " +
                    "FROM users a JOIN profiles b ON a.id = b.user_id " +
                    "WHERE a.status = 'active' AND EXISTS (SELECT 1 FROM logs WHERE user_id = a.id)";

            SQLAnalysisResult result = analyzeSQL(sql);
            System.out.println(result);

            System.out.println("具体信息:");
            System.out.println("表数量: " + result.getTableCount());
            System.out.println("涉及的表: " + result.getTableNames());
            System.out.println("字段引用的表数量: " + result.getReferencedTableCount());

        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    private static final Pattern ALIAS_DOT_PROP =
//            Pattern.compile(
//                    // 1. 列名（可选表别名）
//                    "\\b(?:\\w+\\.)?([\\w_]+)" +
//                            // 2. as / AS / aS / As + 可选空格 + 可选引号
//                            "\\s+[aA][sS]\\s+[\"']?" +
//                            // 3. 对象.属性（分组）
//                            "(\\w+)\\.(\\w+)" +
//                            // 4. 可选引号 + 单词边界
//                            "[\"']?\\b",
//                    Pattern.CASE_INSENSITIVE
//            );
            Pattern.compile(
                    // 1. 列名（可选表别名）
                    "\\b(?:\\w+\\.)?([\\w_]+)" +
                            // 2. as / AS / aS / As + 可选空格 + 可选引号
                            "\\s+[aA][sS]\\s+[\"']?" +
                            // 3. 对象.属性（分组）—— 不区分大小写
                            "([\\w_]+)\\.([\\w_]+)" +
                            // 4. 可选引号 + 单词边界
                            "[\"']?\\b",
                    Pattern.CASE_INSENSITIVE
            );
    @Test
    void testSqlAlias(){
        String[] samples = {
                "course_id as \"stage.courseId\"",
                "a.course_id AS 'stage.courseId'",
                "b.seq aS stage.seq",
                "u.name As \"user.name\"",
                """
                SELECT a.id AS id, a.course_id AS courseId, a.stage_id AS stageId, a.seq AS seq, a.name AS name, a.duration AS duration, a.outline AS outline, a.content AS content, a.ava_flag AS avaFlag, a.del_flag AS delFlag, a.created_by AS createdBy, a.created_time AS createdTime, a.updated_by AS updatedBy, a.updated_time AS updatedTime, c.id AS "course.id", c.title AS "course.title", c.sub_title AS "course.subTitle", c.cover_url AS "course.coverUrl", c.teacher_id AS "course.teacherId", c.category_id AS "course.categoryId", c.level AS "course.level", c.price AS "course.price", c.vip_price AS "course.vipPrice", c.brief AS "course.brief", c.status AS "course.status", c.ava_flag AS "course.avaFlag", c.del_flag AS "course.delFlag", c.created_by AS "course.createdBy", c.created_time AS "course.createdTime", c.updated_by AS "course.updatedBy", c.updated_time AS "course.updatedTime", b.id AS "stage.id", b.course_id AS "stage.courseId", b.seq AS "stage.seq", b.name AS "stage.name", b.description AS "stage.description", b.lesson_cnt AS "stage.lessonCnt", b.ava_flag AS "stage.avaFlag", b.del_flag AS "stage.delFlag", b.created_by AS "stage.createdBy", b.created_time AS "stage.createdTime", b.updated_by AS "stage.updatedBy", b.updated_time AS "stage.updatedTime" FROM c_lesson AS a JOIN c_course_stage AS b ON b.id = a.stage_id AND b.del_flag = 0 JOIN c_course AS c ON c.id = a.course_id AND c.del_flag = 0 WHERE a.del_flag = :p0 AND (c.TITLE, b.seq, a.SEQ) IN ((:p1, :p2, :p3), (:p4, :p5, :p6))
                """
        };

        for (String sql : samples) {
            Matcher m = ALIAS_DOT_PROP.matcher(sql);
            while (m.find()) {
                String col      = m.group(1);   // course_id / course_id / seq / name
                String objName  = m.group(2);   // stage / stage / stage / user
                String propName = m.group(3);   // courseId / courseId / seq / name
                System.out.printf("列=%s  对象=%s  属性=%s%n", col, objName, propName);
            }
        }
    }
}

